Imports System
Imports System.Net
Imports System.Data
Imports System.Net.Mail
Imports System.Net.NetworkCredential
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

	Public Sub Main()
        Dim str_To As String
        Dim str_From As String
        Dim str_Message As String
        Dim str_Subject As String
        Dim str_OrderNum As String
        Dim str_AmountDue As String
        Dim str_OrderDate As String
        Try
            'Get the values returned by the query of Execute SQL Task
            str_OrderDate = Dts.Variables("User::OrderDate").Value.ToString
            str_OrderNum = Dts.Variables("User::OrderNumber").Value.ToString
            str_AmountDue = Dts.Variables("User::AmountDue").Value.ToString

            'Create Mail Message and Subject
            Dts.Variables("User::MailSubject").Value = "Status for Order: " + str_OrderNum
            Dts.Variables("User::MailMessage").Value = "The order number: " + str_OrderNum + " placed on " + str_OrderDate + " is ready for shipment. Please generate invoice for amount: " + str_AmountDue

            'Read mail attributes from package variable into local variable
            str_To = Dts.Variables("MailTo").Value.ToString
            str_From = Dts.Variables("MailFrom").Value.ToString
            str_Message = Dts.Variables("MailMessage").Value.ToString
            str_Subject = Dts.Variables("MailSubject").Value.ToString

            'Send Mail
            MailSend(str_To, str_From, str_Subject, str_Message)

        Catch ex As Exception

        End Try
		Dts.TaskResult = Dts.Results.Success
	End Sub
    Public Sub MailSend(ByVal ToMail As String, ByVal From As String, ByVal Subject As String, ByVal Message As String)

        Dim smtpclient As New SmtpClient
        Dim mailmessage As New MailMessage
        Dim fromadd As New MailAddress(From)

        'SMTP server will be picked from cinfig file
        smtpclient.Host = Dts.Variables("SmtpServer").Value.ToString
        smtpclient.Port = 25
        smtpclient.Credentials = CredentialCache.DefaultNetworkCredentials
        mailmessage.From = fromadd
        mailmessage.To.Add(ToMail)
        mailmessage.Subject = CStr(Subject)
        mailmessage.Body = CStr(Message)

        smtpclient.Send(mailmessage)

    End Sub
End Class